﻿using BingSpatialDataServiceExcelAddin.BSDSTools.Models;
using BingSpatialDataServiceExcelAddin.Views;
using Microsoft.Office.Tools.Ribbon;
using System;
using System.Collections.Generic;
using System.IO;
using System.Windows.Forms;
using System.Windows.Navigation;
using Excel = Microsoft.Office.Interop.Excel;

namespace BingSpatialDataServiceExcelAddin
{
    public partial class BSDSRibbon
    {
        //http://msdn.microsoft.com/en-us/office/hh128771

        #region Ribbon Events

        private void Ribbon1_Load(object sender, RibbonUIEventArgs e)
        {
        }

        #endregion

        #region Action Buttons

        private void validateBtn_Click(object sender, RibbonControlEventArgs e)
        {
            var sheet = (Excel.Worksheet)Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet;

            bool isValid, includesSchemaInfo;
            string notes = "";
            BSDSTools.FileTools.ColumnHeader header;
            
            DataSourceTools.ValidateDataSource(sheet, out isValid, out includesSchemaInfo, out notes, out header);

            if (isValid)
            {
                MessageBox.Show("Data Source Successfully Validated.");
            }
            else
            {
                MessageBox.Show("Data Source Invalid.\r\n" + notes);
            }
        }

        private void uploadBtn_Click(object sender, RibbonControlEventArgs e)
        {
            try
            {
                UploadModal form = new UploadModal();
                form.Show();                
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void geocodeBtn_Click(object sender, RibbonControlEventArgs e)
        {
            try
            {
                GeocodeModal form = new GeocodeModal();
                form.Show();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        #endregion

        #region Import Buttons

        private void ImportCSVBtn_Click(object sender, RibbonControlEventArgs e)
        {
            ImportDelimitedDataSource(',');
        }

        private void ImportPipeBtn_Click(object sender, RibbonControlEventArgs e)
        {
            ImportDelimitedDataSource('|');
        }

        private void ImportTabBtn_Click(object sender, RibbonControlEventArgs e)
        {
            ImportDelimitedDataSource('\t');
        }

        private void ImportXMLBtn_Click(object sender, RibbonControlEventArgs e)
        {
            var sheet = (Excel.Worksheet)Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet;

            //Check if file is empty
            try
            {
                Excel.Range firstCell = sheet.get_Range("A1", Type.Missing);

                //Check to see if any data exists
                Excel.Range lastCell = (Excel.Range)sheet.Cells[sheet.Cells.Find("*",
                           (Excel.Range)sheet.Cells[1, 1],
                           Excel.XlFindLookIn.xlValues,
                           Excel.XlLookAt.xlWhole,
                           Excel.XlSearchOrder.xlByRows,
                           Excel.XlSearchDirection.xlPrevious,
                           false, false, Type.Missing).Row,
                       sheet.Cells.Find("*",
                           (Excel.Range)sheet.Cells[1, 1],
                           Excel.XlFindLookIn.xlValues,
                           Excel.XlLookAt.xlWhole,
                           Excel.XlSearchOrder.xlByColumns,
                           Excel.XlSearchDirection.xlPrevious,
                           false, false, Type.Missing).Column];

                Excel.Range worksheetCells = sheet.get_Range(firstCell, lastCell);

                if (MessageBox.Show("Data found in the worksheet.\r\nPress Yes to delete data and load template.", "Delete Existing Data", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.No)
                {
                    return;
                }

                worksheetCells.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
            }
            catch { }

            OpenFileDialog ofg = new OpenFileDialog()
            {
                RestoreDirectory = true,
                Title = "Import Data Source",
                Filter = "XML files (*.xml)|*.xml"
            };

            if (ofg.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    System.Data.DataSet dataSet = new System.Data.DataSet();
                    dataSet.ReadXml(ofg.FileName);

                    System.Data.DataTable dt = dataSet.Tables[0];

                    //Add settings to workbook
                    Settings s = new Settings(Globals.ThisAddIn.Application.ActiveWorkbook)
                    {
                        DataSourceName = dataSet.DataSetName,
                        EntityTypeName = dt.TableName,
                        IsPublic = false,
                        Culture = string.Empty,
                        MasterKey = string.Empty,
                        QueryKey = string.Empty
                    };
                    s.Save(Globals.ThisAddIn.Application.ActiveWorkbook);

                    //Add Schema Information
                    sheet.Cells[1, 1] = string.Format(Constants.BingDataSchemaType, dt.TableName);

                    //Get Primary Key
                    System.Data.DataColumn pKey = dt.PrimaryKey[0];

                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        //Set the data type of all columns in excel to text so that numbers do not get incorrectly formatted.
                        (sheet.Cells[2, j + 1] as Excel.Range).EntireColumn.NumberFormat = "@";
                    }

                    //Write data to file
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            if (i == 0)
                            {
                                //Write Column Names
                                if (dt.Columns[j] == pKey)
                                {
                                    sheet.Cells[2, j + 1] = string.Format("{0}({1},primaryKey)", dt.Columns[j].ColumnName, DataTypes.GetTypeName(dt.Columns[j].DataType));
                                }
                                else
                                {
                                    sheet.Cells[2, j + 1] = string.Format("{0}({1})", dt.Columns[j].ColumnName, DataTypes.GetTypeName(dt.Columns[j].DataType));
                                }
                            }

                            sheet.Cells[i + 3, j + 1] = dt.Rows[i][j].ToString();
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Error importing Data Source:\r\n" + ex.Message);
                }
            }
        }

        #endregion

        #region Export Buttons

        private void csvExportBtn_Click(object sender, RibbonControlEventArgs e)
        {
            ExportDelimitedDataSource(',');
        }

        private void pipeExportBtn_Click(object sender, RibbonControlEventArgs e)
        {
            ExportDelimitedDataSource('|');
        }

        private void tabExportBtn_Click(object sender, RibbonControlEventArgs e)
        {
            ExportDelimitedDataSource('\t');
        }

        private void xmlExportBtn_Click(object sender, RibbonControlEventArgs e)
        {
            //http://msdn.microsoft.com/en-us/library/system.data.datatable.writexml.aspx
            //http://msdn.microsoft.com/en-us/library/gg585138.aspx
            //http://www.aspspider.com/resources/Resource510.aspx

            SaveFileDialog sfg = new SaveFileDialog()
            {
                RestoreDirectory = true,
                Title = "Export Data Source",
                DefaultExt = "xml",
                Filter = "xml files (*.xml)|*.xml"
            };

            if (sfg.ShowDialog() == DialogResult.OK)
            {
                var sheet = (Excel.Worksheet)Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet;

                using (FileStream fs = new FileStream(sfg.FileName, FileMode.OpenOrCreate))
                {
                    string log;
                    System.Data.DataSet dataSet;

                    DataSourceTools.GenerateDataSet(sheet, out dataSet, out log);

                    if (dataSet != null)
                    {
                        dataSet.WriteXml(fs, System.Data.XmlWriteMode.WriteSchema);
                    }else{
                        log += "\r\nUnable to generate XML data source.\r\n";
                    }

                    if (string.IsNullOrWhiteSpace(log))
                    {
                        MessageBox.Show("Data source successfully exported.");
                    }
                    else
                    {
                        DisplayOutputPanel("Export Failed", log);

                        //Delete the failed file.
                        FileInfo fInfo = new FileInfo(sfg.FileName);
                        fInfo.Delete();
                    }
                }
            }
        }

        #endregion

        #region Options Button

        private void LoadTemplateBtn_Click(object sender, RibbonControlEventArgs e)
        {
            var sheet = (Excel.Worksheet)Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet;

            try
            {
                Excel.Range firstCell = sheet.get_Range("A1", Type.Missing);

                //Check to see if any data exists
                Excel.Range lastCell = (Excel.Range)sheet.Cells[sheet.Cells.Find("*",
                           (Excel.Range)sheet.Cells[1, 1],
                           Excel.XlFindLookIn.xlValues,
                           Excel.XlLookAt.xlWhole,
                           Excel.XlSearchOrder.xlByRows,
                           Excel.XlSearchDirection.xlPrevious,
                           false, false, Type.Missing).Row,
                       sheet.Cells.Find("*",
                           (Excel.Range)sheet.Cells[1, 1],
                           Excel.XlFindLookIn.xlValues,
                           Excel.XlLookAt.xlWhole,
                           Excel.XlSearchOrder.xlByColumns,
                           Excel.XlSearchDirection.xlPrevious,
                           false, false, Type.Missing).Column];

                Excel.Range worksheetCells = sheet.get_Range(firstCell, lastCell);

                if (MessageBox.Show("Data found in the worksheet.\r\nPress Yes to delete data and load template.", "Delete Existing Data", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.No)
                {
                    return;
                }

                worksheetCells.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
            }
            catch { }

            object[,] template = new object[3, 9];

            template[0, 0] = "Bing Spatial Data Services, 1.0, Location";

            template[1, 0] = "EntityID(Edm.String,primaryKey)";
            template[1, 1] = "Name(Edm.String)";
            template[1, 2] = "AddressLine(Edm.String)";
            template[1, 3] = "Locality(Edm.String)";
            template[1, 4] = "PostalCode(Edm.String)";
            template[1, 5] = "AdminDistrict(Edm.String)";
            template[1, 6] = "CountryRegion(Edm.String)";
            template[1, 7] = "Latitude(Edm.Double)";
            template[1, 8] = "Longitude(Edm.Double)";

            template[2, 0] = "1";
            template[2, 1] = "Microsoft";
            template[2, 2] = "1 Microsoft Way";
            template[2, 3] = "Redmond";
            template[2, 4] = "8600";
            template[2, 5] = "WA";
            template[2, 6] = "United States";
            template[2, 7] = 47.6405683;
            template[2, 8] = -122.129373;

            Excel.Range c1 = (Excel.Range)sheet.Cells[1, 1];
            Excel.Range c2 = (Excel.Range)sheet.Cells[3, 9];
            Excel.Range range = sheet.get_Range(c1, c2);
            range.Value = template;
        }

        private void settingsBtn_Click(object sender, RibbonControlEventArgs e)
        {
            SettingsForm form = new SettingsForm();
            NativeWindow xlMain = new NativeWindow();
            xlMain.AssignHandle(new IntPtr(Globals.ThisAddIn.Application.Hwnd));

            form.ShowDialog(xlMain);
            form.Dispose();
            xlMain.ReleaseHandle();
        }

        private void DSManagerBtn_Click(object sender, RibbonControlEventArgs e)
        {
            Globals.ThisAddIn.DataSourceManager.Visible = true;
        }

        #endregion

        #region Resources

        private void BingMapsPortalBtn_Click(object sender, RibbonControlEventArgs e)
        {
            OpenWebPage("http://bingmapsportal.com");
        }

        private void MSDNDocsBtn_Click(object sender, RibbonControlEventArgs e)
        {
            OpenWebPage("http://msdn.microsoft.com/en-us/library/ff701734.aspx");
        }

        private void SchemaDocsBtn_Click(object sender, RibbonControlEventArgs e)
        {
            OpenWebPage("http://msdn.microsoft.com/en-us/library/gg585138.aspx");
        }

        #endregion

        #region Private Methods

        private void ExportDelimitedDataSource(char delimiter)
        {
            SaveFileDialog sfg = new SaveFileDialog()
            {
                RestoreDirectory = true,
                Title = "Export Data Source",
                DefaultExt = "txt",
                Filter = (delimiter == ',') ? "txt files (*.txt)|*.txt|CSV (*.csv)|*.csv|All files (*.*)|*.*" : "txt files (*.txt)|*.txt|All files (*.*)|*.*",
            };

            if (sfg.ShowDialog() == DialogResult.OK)
            {
                var sheet = (Excel.Worksheet)Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet;
                string logs = null;
                
                Settings bsdsSettings = new Settings(Globals.ThisAddIn.Application.ActiveWorkbook);

                using (FileStream fs = new FileStream(sfg.FileName, FileMode.OpenOrCreate))
                {
                    logs = DataSourceTools.ExportDelimitedDataSource(sheet, fs, delimiter, bsdsSettings);
                }

                if (string.IsNullOrWhiteSpace(logs))
                {
                    MessageBox.Show("Data source successfully exported.");
                }
                else
                {
                    DisplayOutputPanel("Export Failed", logs);

                    //Delete the failed file.
                    FileInfo fInfo = new FileInfo(sfg.FileName);
                    fInfo.Delete();
                }
            }
        }

        private void ImportDelimitedDataSource(char delimiter)
        {
            var sheet = (Excel.Worksheet)Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet;

            //Check if file is empty
            try
            {
                Excel.Range firstCell = sheet.get_Range("A1", Type.Missing);

                //Check to see if any data exists
                Excel.Range lastCell = (Excel.Range)sheet.Cells[sheet.Cells.Find("*",
                           (Excel.Range)sheet.Cells[1, 1],
                           Excel.XlFindLookIn.xlValues,
                           Excel.XlLookAt.xlWhole,
                           Excel.XlSearchOrder.xlByRows,
                           Excel.XlSearchDirection.xlPrevious,
                           false, false, Type.Missing).Row,
                       sheet.Cells.Find("*",
                           (Excel.Range)sheet.Cells[1, 1],
                           Excel.XlFindLookIn.xlValues,
                           Excel.XlLookAt.xlWhole,
                           Excel.XlSearchOrder.xlByColumns,
                           Excel.XlSearchDirection.xlPrevious,
                           false, false, Type.Missing).Column];

                Excel.Range worksheetCells = sheet.get_Range(firstCell, lastCell);

                if (MessageBox.Show("Data found in the worksheet.\r\nPress Yes to delete data and load template.", "Delete Existing Data", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.No)
                {
                    return;
                }

                worksheetCells.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
            }
            catch { }

            OpenFileDialog ofg = new OpenFileDialog()
            {
                RestoreDirectory = true,
                Title = "Import Data Source",
                DefaultExt = "txt",
                Filter = (delimiter == ',') ? "txt files (*.txt)|*.txt|CSV (*.csv)|*.csv|All files (*.*)|*.*" : "txt files (*.txt)|*.txt|All files (*.*)|*.*",
            };

            if (ofg.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    using (FileStream fileStream = new FileStream(ofg.FileName, FileMode.Open))
                    {
                        using (BingSpatialDataServiceExcelAddin.BSDSTools.FileTools.DataSourceReader reader = new BingSpatialDataServiceExcelAddin.BSDSTools.FileTools.DataSourceReader(fileStream, delimiter))
                        {
                            BSDSTools.FileTools.ColumnHeader header = reader.Header;

                            //Add settings to workbook
                            Settings s = new Settings(Globals.ThisAddIn.Application.ActiveWorkbook)
                            {
                                EntityTypeName = reader.EntityType,
                                IsPublic = false,
                                Culture = string.Empty,
                                MasterKey = string.Empty,
                                QueryKey = string.Empty
                            };
                            s.Save(Globals.ThisAddIn.Application.ActiveWorkbook);

                            //Add Schema Information
                            sheet.Cells[1, 1] = string.Format(Constants.BingDataSchemaType, reader.EntityType);
                            
                            //Add Column Header
                            IList<string> columns = header.GetColumnNames(true);
                            for (int i = 0; i < columns.Count; i++)
                            {
                                //Set the data type of all columns in excel to text so that numbers do not get incorrectly formatted.
                                (sheet.Cells[2, i + 1] as Excel.Range).EntireColumn.NumberFormat = "@";
                                sheet.Cells[2, i + 1] = columns[i];
                            }

                            string[] dsRow = reader.GetNextRow();
                            int index = 3;

                            //Import each row of the data source
                            while (dsRow != null)
                            {    
                                for (int i = 0; i < dsRow.Length; i++)
                                {
                                    sheet.Cells[index, i + 1] = dsRow[i];
                                }

                                //get next from from data source
                                dsRow = reader.GetNextRow();
                                index++;
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Error importing Data Source:\r\n" + ex.Message);
                }
            }
        }

        private void DisplayOutputPanel(string title, string output)
        {
            OutputModal form = new OutputModal(title, output);
            NativeWindow xlMain = new NativeWindow();
            xlMain.AssignHandle(new IntPtr(Globals.ThisAddIn.Application.Hwnd));

            form.ShowDialog(xlMain);
            form.Dispose();
            xlMain.ReleaseHandle();
        }

        private void OpenWebPage(string url)
        {
            NavigationWindow window = new NavigationWindow();
            if (window != null)
            {
                window.Navigate(new Uri(url, UriKind.Absolute));
                window.Show();
            }
        }
        #endregion
    }
}
